﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using zj.Models;

namespace zj.DAL
{
    /// <summary>
    /// 设备类型和协议类型的查询
    /// </summary>
    public class EquipmentsService
    {

        /// <summary>
        /// 获取所有的设备类型
        /// </summary>
        /// <returns></returns>
        public List<EquipmentType> GetAllEtype()
        {
            string sql = "SELECT EtypeId ,EtypeName FROM EquipmentType ";
            List<EquipmentType> list = new List<EquipmentType>();
            SqlDataReader sqlDataReader = SQLHelper.ExecuteReader(sql, null);
            while (sqlDataReader.Read())
            {
                list.Add(new EquipmentType
                {
                    EtypeId = Convert.ToInt32(sqlDataReader["EtypeId"]),
                    EtypeName = sqlDataReader["EtypeName"].ToString()

                });
            }
            sqlDataReader.Close();
            return list;
        }

        /// <summary>
        /// 根据设备类型来获取对应的协议类型
        /// </summary>
        /// <param name="etypeId"></param>
        /// <returns></returns>
        public List<ProtocalType> GetProtocalTypesByEtypeId(int etypeId)
        {
            string sql = "SELECT PtypeId ,PtypeName FROM ProtocalType  WHERE EtypeId = @EtypeId ";
            SqlParameter[] sqlParameters = {
                new SqlParameter("EtypeId",etypeId),
            };
            List<ProtocalType> protocalTypes = new List<ProtocalType>();
            SqlDataReader sqlDataReader = SQLHelper.ExecuteReader(sql, sqlParameters);
            while (sqlDataReader.Read())
            {
                protocalTypes.Add(new ProtocalType
                {
                    PtypeId = Convert.ToInt32(sqlDataReader["PtypeId"]),
                    PtypeName = sqlDataReader["PtypeName"].ToString()
                });
            }
            sqlDataReader.Close();
            return protocalTypes;
        }
        /// <summary>
        /// 插入对象
        /// </summary>
        /// <param name="equipments"></param>
        /// <returns></returns>
        public int Insert(Equipments equipments)
        {
            string sql = "INSERT INTO [Equipments](ProjectId, EtypeId, PtypeId, EquipmentName," +
                " IpAddress, PortNo," +
                " SerialNo, BaudRate, DataBit, ParityBit, StopBit, " +
                "OPCNodeNme, OPCServerName," +
                " IsEnable, Comments)";
            sql += "VALUES(@ProjectId, @EtypeId, @PtypeId, @EquipmentName,";
            sql += "@IpAddress,@PortNo,";
            sql += " @SerialNo, @BaudRate, @DataBit, @ParityBit, @StopBit, ";
            sql += " @IsEnable, @Comments);";
            sql += "SELECT @@Identity";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@ProjectId",equipments.ProjectId),
                new SqlParameter("@EtypeId",equipments.EtypeId),
                new SqlParameter("@PtypeId",equipments.PtypeId),
                new SqlParameter("@EquipmentName",equipments.EquipmentName),
                new SqlParameter("@IpAddress",equipments.IpAddress),
                new SqlParameter("@PortNo",equipments.PortNo),
                new SqlParameter("@SerialNo",equipments.SerialNo),
                new SqlParameter("@BaudRate",equipments.BaudRate),
                new SqlParameter("@DataBit",equipments.DataBit),
                new SqlParameter("@ParityBit",equipments.ParityBit),
                new SqlParameter("@StopBit",equipments.StopBit),
                new SqlParameter("@IsEnable",equipments.IsEnable),
                new SqlParameter("@Comments",equipments.Comments),
            };
            return Convert.ToInt32( SQLHelper.ExecuteScalar(sql, sqlParameters));
        }
        /// <summary>
        /// 同一个项目中不允许有重复设备名称，验证
        /// </summary>
        /// <param name="eName"></param>
        /// <param name="pId"></param>
        /// <returns>false 不重复    true 重复</returns>
        public bool IsRepeatForInsert(string eName,int pId)
        {
            string sql = "SELECT COUNT(*) FROM Equipment WHERE EquipmentName=@EquipmentName AND ProjectId= @ProjectId";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                new SqlParameter("@EquipmentName",eName),
                new SqlParameter("@ProjectId",pId)
            };
            return Convert.ToInt32(SQLHelper.ExecuteScalar(sql, sqlParameters)) == 0 ? false : true;
        }
    }
}
